/*
SELECT * FROM fn_load_all_ship_alert_emails( 1240 )
*/
ALTER function fn_load_all_ship_alert_emails ( @customer_shipping_id int )
RETURNS @fn_results Table ( Customer_Id int, Contact_Person varchar(50), Email varchar(50), Source varchar(50) )

AS
BEGIN
	declare @salesOrder_Id int
	declare @customer_name varchar(65)
	declare @salesOrder_contact_person varchar(50)
	declare @so_def_bill_contact_person varchar(50)
	declare @so_def_ship_contact_person varchar(50)
	declare @customer_id int
	declare @odm_id int
	declare @email_count int
	declare @shipping_address_id int
	declare @billing_address_id int
	declare @contact_person varchar(50)
	declare @email varchar(50)
	declare @sourcePerson varchar(50)
	DECLARE @SALES_PERSON_NAME VARCHAR(50)

	declare @emailCursor CURSOR

 	declare @results Table ( Customer_Id int, Contact_Person varchar(50), Email varchar(50), Source varchar(50) )
 	
	SELECT @salesOrder_Id = SalesOrder_ID, @odm_id = CST_ODM_ID FROM Customer_Shipping_Table WHERE CustomerShipping_ID = @customer_shipping_id
	SELECT @salesOrder_contact_person = SO_Contact_Person, @so_def_bill_contact_person = SO_Billing_Contact_Person, @so_def_ship_contact_person = SO_Shipping_Contact_Person FROM Sales_Order WHERE SalesOrder_Id = @salesOrder_Id
	SELECT @customer_id = Customer_ID, @shipping_address_id = SO_Shipping_Address_ID, @billing_address_id = SO_Billing_Address_ID, @SALES_PERSON_NAME = SO_SALES_PERSON FROM Sales_Order Where SalesOrder_Id = @salesOrder_Id
	SELECT @customer_name = C_Name FROM Customer WHERE Customer_ID = @customer_id

	--DECLARE @PRINT_MSG VARCHAR (200)
	--SET @PRINT_MSG = ' @customer_id = ' + CONVERT(VARCHAR,@customer_id)+ ' @customer_name = ' + CONVERT(VARCHAR,@customer_name) + ' @shipping_address_id = ' + CONVERT(VARCHAR,@shipping_address_id) + ' @billing_address_id = ' + CONVERT(VARCHAR,@billing_address_id)

	SET @emailCursor = CURSOR FOR SELECT Contact_Person, Email, Source FROM fn_load_customer_ship_alert_emails( @customer_id ) WHERE (Email IS NOT NULL) AND (Email <> '')
	OPEN @emailCursor	
  	FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @sourcePerson

 	WHILE @@fetch_status = 0
 	BEGIN
 		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, @sourcePerson )

		set @contact_person = null
		set @email = null		
		set @sourcePerson = null
 		FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @sourcePerson
 	END
 	CLOSE @emailCursor
	DEALLOCATE @emailCursor

	set @contact_person = null
	set @email = null		
	set @sourcePerson = null

	IF( @odm_id > 0 )
	BEGIN
		SET @emailCursor = CURSOR FOR SELECT Contact_Person, Email, Source  FROM fn_load_customer_ship_alert_emails( @odm_id ) WHERE (Email IS NOT NULL) AND (Email <> '')
		OPEN @emailCursor	
	  	FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @sourcePerson
	
	 	WHILE @@fetch_status = 0
	 	BEGIN
	 		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, @sourcePerson )
	
			set @contact_person = null
			set @email = null		
			set @sourcePerson = null
	 		FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @sourcePerson
	 	END
	 	CLOSE @emailCursor
		DEALLOCATE @emailCursor
	END
		
 	
	---LOAD CUSTOMER EMAIL
	/*IF( @salesOrder_contact_person = @customer_name )
	BEGIN
		SELECT @contact_person = C.C_Contact_Person, @email = A.Email FROM Customer C INNER JOIN Address A ON C.C_Default_Billing_Address_ID = A.Address_ID WHERE ( C.Customer_ID = @customer_id )
		IF ( @email IS NOT NULL AND RTRIM( LTRIM( @email ) ) <> '' )
		BEGIN
			INSERT INTO @results VALUES (@customer_id, @customer_name, @email, 'CUSTOMER' )--here contact name = customer_name 
		END
	
		set @contact_person = null
		set @email = null		

		SELECT @email = Email FROM Sales_Order_Address WHERE salesOrder_Id = @salesOrder_Id
		IF ( @email IS NOT NULL AND RTRIM( LTRIM( @email ) ) <> '' )
		BEGIN
			INSERT INTO @results VALUES ( @customer_id, @customer_name, @email, 'SALESORDER-CUSTOMER' )
		END

		set @contact_person = null
		set @email = null			
	END
	ELSE
	BEGIN
		IF( @salesOrder_contact_person <> '' )
		BEGIN
			SELECT @email = Email FROM Customer_Contact_Persons_Table WHERE  ( Email IS NOT NULL ) AND ( Email <> '' ) AND Customer_Id = @customer_id and Contact_Person = @salesOrder_contact_person
			IF ( @email IS NOT NULL AND RTRIM( LTRIM( @email ) ) <> '' )
			BEGIN
				INSERT INTO @results VALUES ( @customer_id, @salesOrder_contact_person, @email, 'SALESORDER-CUSTOMER' )
			END
	
			set @contact_person = null
			set @email = null
		END
	END*/

 	
	---LOAD ALL USER EMAIL
	SET @emailCursor = CURSOR FOR SELECT First_Name, Email FROM User_Table WHERE (Email IS NOT NULL) AND (Email <> '') AND (Disabled = 1) AND (Ship_Alerts = 0)
	OPEN @emailCursor	
  	FETCH NEXT FROM @emailCursor INTO @contact_person,@email

 	WHILE @@fetch_status = 0
 	BEGIN
 		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, 'USER' )

		set @contact_person = null
		set @email = null		
 		FETCH NEXT FROM @emailCursor INTO @contact_person,@email
 	END
 	CLOSE @emailCursor
	DEALLOCATE @emailCursor
 	
	--TO LOAD SALES PERSON EMAIL
	SET @email = NULL
	SELECT  @email = EMAIL FROM SALES_PERSONS SP, ADDRESS A WHERE SP.SALESPERSON_NAME = @SALES_PERSON_NAME AND A.ADDRESS_ID = SP.ADDRESS_ID AND SP.SHIP_ALERT_MAIL = 0 AND DEACTIVATED = 0
	IF ( @email IS NOT NULL )
	 BEGIN
		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, 'SALES PERSON' )
	 END

	insert @fn_results select * from @results
 	return
 end
 